package pl.register.android.database.crud;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import pl.register.android.chapter.ChapterTitle;
import pl.register.android.classes.ClassesTopic;
import pl.register.android.database.dao.ProgramDao;
import pl.register.android.database.tables.LessonProgramDBTable;
import pl.register.android.database.tables.ProgramInGroupDBTable;
import pl.register.android.group.StudentsGroup;
import pl.register.android.homework.HomeworkTopic;
import pl.register.android.lesson.LessonProgram;
import pl.register.android.lesson.TagLesson;
import pl.register.android.program.Program;
import pl.register.android.year.Year;

import java.util.ArrayList;
import java.util.List;


/**
 * @author Mateusz Strzelecki
 * @since 2013-04-26
 */
public class ProgramCRUD implements ProgramDao {

    private SQLiteDatabase database;

    private ProgramInGroupCRUD programInGroupCRUD;

    private ClassesTopicCRUD classesTopicCRUD;

    private HomeworkTopicCRUD homeworkTopicCRUD;

    private ChapterTitleCRUD chapterTitleCRUD;

    public ProgramCRUD(SQLiteDatabase database) {
        this.database = database;
        programInGroupCRUD = new ProgramInGroupCRUD(database);
        classesTopicCRUD = new ClassesTopicCRUD(database);
        homeworkTopicCRUD = new HomeworkTopicCRUD(database);
        chapterTitleCRUD = new ChapterTitleCRUD(database);
    }

    //GET

    private LessonProgram getLessonProgramBy(Long id) {
        LessonProgram lessonProgram = null;
        String where = LessonProgramDBTable.ID + " = " + id;
        Cursor cursor = database.query(LessonProgramDBTable.TABLE_NAME, null, where, null, null, null, null);
        if (cursor.moveToFirst()) {
            Long lessonProgramId = cursor.getLong(cursor.getColumnIndex(LessonProgramDBTable.ID));
            int lessonOrder = cursor.getInt(cursor.getColumnIndex(LessonProgramDBTable.ORDER));
            lessonProgram = new LessonProgram(lessonProgramId, lessonOrder);

            List<ClassesTopic> classesTopics = classesTopicCRUD.findClassesBy(lessonProgram);
            lessonProgram.setClassesTopics(classesTopics);
            HomeworkTopic homeworkTopic = homeworkTopicCRUD.findHomeworkTopicBy(lessonProgram);
            lessonProgram.setHomeworkTopic(homeworkTopic);
            ChapterTitle chapterTitle = chapterTitleCRUD.findChapterBy(lessonProgram);
            lessonProgram.setChapterTitle(chapterTitle);
        }
        cursor.close();

        return lessonProgram;
    }

    @Override
    public Program findProgramFor(Year year) {
        List<LessonProgram> lessonPrograms = new ArrayList<LessonProgram>();

        String where = LessonProgramDBTable.FK_YEAR_ID + " = " + year.getId();
        String orderBy = LessonProgramDBTable.ORDER;
        Cursor cursor = database.query(LessonProgramDBTable.TABLE_NAME, null, where, null, null, null, orderBy);
        while (cursor.moveToNext()) {
            Long lessonProgramId = cursor.getLong(cursor.getColumnIndex(LessonProgramDBTable.ID));
            int lessonOrder = cursor.getInt(cursor.getColumnIndex(LessonProgramDBTable.ORDER));
            LessonProgram lessonProgram = new LessonProgram(lessonProgramId, lessonOrder);

            List<ClassesTopic> classesTopics = classesTopicCRUD.findClassesBy(lessonProgram);
            lessonProgram.setClassesTopics(classesTopics);
            HomeworkTopic homeworkTopic = homeworkTopicCRUD.findHomeworkTopicBy(lessonProgram);
            lessonProgram.setHomeworkTopic(homeworkTopic);
            ChapterTitle chapterTitle = chapterTitleCRUD.findChapterBy(lessonProgram);
            lessonProgram.setChapterTitle(chapterTitle);

            lessonPrograms.add(lessonProgram);
        }
        cursor.close();

        return new Program(lessonPrograms);
    }

    @Override
    public Program findProgramForGroupBy(StudentsGroup group, TagLesson tag) {
        List<LessonProgram> lessonPrograms = new ArrayList<LessonProgram>();
        String query = "SELECT " +
                LessonProgramDBTable.LESSONPROGRAM_ID + ", " +
                ProgramInGroupDBTable.PROGRAMINGROUP_CLASSES_DATE_IN_MS + ", " +
                ProgramInGroupDBTable.PROGRAMINGROUP_HOMEWORK_DATE_IN_MS + ", " +
                ProgramInGroupDBTable.PROGRAMINGROUP_TAG +
                " FROM " + LessonProgramDBTable.TABLE_NAME +
                " INNER JOIN " + ProgramInGroupDBTable.TABLE_NAME +
                " ON " + LessonProgramDBTable.LESSONPROGRAM_ID + " = " + ProgramInGroupDBTable.PROGRAMINGROUP_FK_LESSON_PROGRAM_ID +
                " WHERE " + ProgramInGroupDBTable.PROGRAMINGROUP_FK_STUDENT_GROUP_ID + " = " + group.getId() +
                " AND " + ProgramInGroupDBTable.PROGRAMINGROUP_TAG + " = '" + tag.toString() + "';";
        Cursor cursor = database.rawQuery(query, null);
        while (cursor.moveToNext()) {
            Long lessonProgramId = cursor.getLong(cursor.getColumnIndex(LessonProgramDBTable.ID));
            Long classesDateInMs = cursor.getLong(cursor.getColumnIndex(ProgramInGroupDBTable.CLASSES_DATE_IN_MS));
            Long homeworkDateInMs = cursor.getLong(cursor.getColumnIndex(ProgramInGroupDBTable.HOMEWORK_DATE_IN_MS));
            String tagString = cursor.getString(cursor.getColumnIndex(ProgramInGroupDBTable.TAG));

            LessonProgram lessonProgram = getLessonProgramBy(lessonProgramId);
            lessonProgram.setClassesDate(classesDateInMs);
            lessonProgram.setHomeworkDate(homeworkDateInMs);
            lessonProgram.setTagLesson(TagLesson.valueOf(tagString));
            lessonPrograms.add(lessonProgram);
        }
        cursor.close();

        return new Program(lessonPrograms);
    }

    @Override
    public int countLessonProgramIn(StudentsGroup studentsGroup) {
        int countLessonsProgram;
        String query = "SELECT " +
                LessonProgramDBTable.LESSONPROGRAM_ID +
                " FROM " + LessonProgramDBTable.TABLE_NAME +
                " INNER JOIN " + ProgramInGroupDBTable.TABLE_NAME +
                " ON " + LessonProgramDBTable.LESSONPROGRAM_ID + " = " + ProgramInGroupDBTable.PROGRAMINGROUP_FK_LESSON_PROGRAM_ID +
                " WHERE " + ProgramInGroupDBTable.PROGRAMINGROUP_FK_STUDENT_GROUP_ID + " = " + studentsGroup.getId() + ";";
        Cursor cursor = database.rawQuery(query, null);
        countLessonsProgram = cursor.getCount();
        cursor.close();
        return countLessonsProgram;
    }

    //INSERT
    @Override
    public Program insertProgram(Program program, Year year) {
        for (LessonProgram lessonProgram : program.getLessons()) {
            insertProgramLesson(lessonProgram, year);
        }
        return program;
    }

    private LessonProgram insertProgramLesson(LessonProgram lessonProgram, Year year) {
        ContentValues insertValues = new ContentValues();
        insertValues.put(LessonProgramDBTable.ORDER, lessonProgram.getOrder());
        insertValues.put(LessonProgramDBTable.FK_YEAR_ID, year.getId());
        long rowId = database.insert(LessonProgramDBTable.TABLE_NAME, null, insertValues);
        lessonProgram.setId(rowId);

        for (ClassesTopic classesTopic : lessonProgram.getClassesTopics()) {
            classesTopicCRUD.insertClassesFor(classesTopic, lessonProgram);
        }
        homeworkTopicCRUD.insertHomeworkTopic(lessonProgram.getHomeworkTopic(), lessonProgram);
        chapterTitleCRUD.insertChapter(lessonProgram.getChapterTitle(), lessonProgram);
        return lessonProgram;
    }

    public LessonProgram insertLessonProgramTo(LessonProgram lessonProgram, StudentsGroup group) {
        return programInGroupCRUD.addProgramForGroup(lessonProgram, group);
    }

    @Override
    public LessonProgram findNextLessonProgram(LessonProgram prevLessonProgram, Year year) {
        LessonProgram lessonProgram = null;
        if (prevLessonProgram == null) {
            return lessonProgram;
        }
        String where = LessonProgramDBTable.FK_YEAR_ID + " = " + year.getId() +
                " AND " + LessonProgramDBTable.LESSONPROGRAM_ORDER + " = " + (prevLessonProgram.getOrder() + 1);
        Cursor cursor = database.query(LessonProgramDBTable.TABLE_NAME, null, where, null, null, null, null);
        if (cursor.moveToFirst()) {
            Long lessonProgramId = cursor.getLong(cursor.getColumnIndex(LessonProgramDBTable.ID));
            int lessonOrder = cursor.getInt(cursor.getColumnIndex(LessonProgramDBTable.ORDER));
            lessonProgram = new LessonProgram(lessonProgramId, lessonOrder);

            List<ClassesTopic> classesTopics = classesTopicCRUD.findClassesBy(lessonProgram);
            lessonProgram.setClassesTopics(classesTopics);
            HomeworkTopic homeworkTopic = homeworkTopicCRUD.findHomeworkTopicBy(lessonProgram);
            lessonProgram.setHomeworkTopic(homeworkTopic);
            ChapterTitle chapterTitle = chapterTitleCRUD.findChapterBy(lessonProgram);
            lessonProgram.setChapterTitle(chapterTitle);
        }
        cursor.close();
        return lessonProgram;
    }

    public void updateLessonProgramIn(LessonProgram lessonProgram, StudentsGroup group) {
        programInGroupCRUD.updateProgram(lessonProgram, group);
    }


    //UPDATE
    @Override
    public void updateLessonProgram(LessonProgram lessonProgram) {
        List<ClassesTopic> classesTopics = lessonProgram.getClassesTopics();
        for (ClassesTopic classesTopic : classesTopics) {
            classesTopicCRUD.updateClassesTopic(classesTopic);
        }
        homeworkTopicCRUD.updateHomeworkTopic(lessonProgram.getHomeworkTopic());
        chapterTitleCRUD.updateChapterTitle(lessonProgram.getChapterTitle());
    }

    @Override
    public void updateClassesTopic(ClassesTopic classesTopic) {
        classesTopicCRUD.updateClassesTopic(classesTopic);
    }

    @Override
    public void updateHomeworkTopic(HomeworkTopic homeworkTopic) {
        homeworkTopicCRUD.updateHomeworkTopic(homeworkTopic);
    }

    //DELETE
    @Override
    public void deleteLessonProgramFrom(LessonProgram lessonProgram, StudentsGroup group) {
        programInGroupCRUD.deleteProgram(lessonProgram, group);
    }
}